# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from twisted.enterprise import adbapi
from openpyxl import load_workbook
import json

class NubiantokyoPipeline:

    def __init__(self, dbpool):
        self.dbpool = dbpool
        wb = load_workbook("./styletable.xlsx")
        ws = wb['分类表']
        rows = list(ws.rows)[1:]
        keys = ['l1_id', 'l1_name', 'l2_id', 'l2_name', 'l3_id', 'l3_name']
        self.goods_class_table = []
        for row in rows:
            row_data = [cell.value for cell in row]
            row_dict = dict(zip(keys, row_data))
            self.goods_class_table.append(row_dict)

        ws = wb['需填写']
        rows = list(ws.rows)[1:]
        self.goods_class_map = {}
        for row in rows:
            self.goods_class_map[row[0].value] = row[1].value
        print(self.goods_class_map)

    def set_gc_id(self, item):
        for row in self.goods_class_table:
            if row['l3_id'] == self.goods_class_map[item['category_name']]:
                item['one_classid'] = row['l1_id']
                item['two_classid'] = row['l2_id']
                item['three_classid'] = row['l3_id']

    @classmethod
    def from_settings(cls, settings):  # 函数名固定，会被scrapy调用，直接可用settings的值
        """
        数据库建立连接
        :param settings: 配置参数
        :return: 实例化参数
        """
        adbparams = dict(
            host=settings['MYSQL_HOST'],
            port=settings['MYSQL_PORT'],
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            password=settings['MYSQL_PASSWORD'],
            cursorclass=pymysql.cursors.DictCursor  # 指定cursor类型
        )

        # 连接数据池ConnectionPool，使用pymysql或者Mysqldb连接
        dbpool = adbapi.ConnectionPool('pymysql', **adbparams)
        # 返回实例化参数
        return cls(dbpool)

    def process_item(self, item, spider):

        # 处理item
        def replace_path(image):
            image['path'] = image['path'].replace('full', 'nubiantokyo')
            return image
        item['images'] = list(map(replace_path,item['images']))
        item['thumbs'] = item['images'][0]['path']+'%@%cloudtype=2'
        item['pictures'] = ','.join(list(map(lambda x:x['path']+'%@%cloudtype=2',item['images'])))
        specs = item['specs']
        for (i, v) in enumerate(specs):
            specs[i]['thumb'] = item['thumbs']
        item['specs'] = json.dumps(specs)

        if self.goods_class_map[item['category_name']]:
            self.set_gc_id(item)
            # 使用twisted将MySQL插入变成异步执行。通过连接池执行具体的sql操作，返回一个对象
            query = self.dbpool.runInteraction(self.do_insert, item)  # 指定操作方法和操作数据
            # 添加异常处理
            query.addCallback(self.handle_error)  # 处理异常


    def do_insert(self, cursor, item):
        # 对数据库进行插入操作，并不需要commit，twisted会自动commit
        insert_sql = """
        insert into cmf_shop_goods(uid,name,one_classid,two_classid,three_classid,thumbs,content,pictures,specs,source,status) VALUES ('88888',%s,%s,%s,%s,%s,%s,%s,%s,%s,-1)
        """

        cursor.execute(insert_sql, (item['name'], item['one_classid'], item['two_classid'], item['three_classid'], item['thumbs'], item['content'],item['pictures'], item['specs'],item['source']))

    def handle_error(self, failure):
        if failure:
            # 打印错误信息
            print(failure)
